package model.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import model.dto.League;
import utility.Database;
import utility.Log;

public class LeagueModel extends Database {
	public boolean insert(League dto) {
		String sql = "insert into tbl_league(league_id,league_name,league_image,country_id) values(null,?,?,?)";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, dto.getName());
			pstmt.setString(2, dto.getImage());
			pstmt.setInt(3, dto.getCountryId());
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return false;
	}

	public boolean delete(int league_id) {
		String sql = "delete from tbl_league where league_id=?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, league_id);
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return false;
	}

	public boolean update(League dto) {
		String sql = "update tbl_league set league_name=?, league_image=?,country_id=? where league_id=?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, dto.getName());
			pstmt.setString(2, dto.getImage());
			pstmt.setInt(3, dto.getCountryId());
			pstmt.setInt(4, dto.getId());
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return false;
	}

	public League get(int league_id) {
		String sql = "SELECT l.*,c.country_name,c.country_image FROM (tbl_country c INNER JOIN tbl_league l ON c.country_id=l.country_id) where l.league_id=?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, league_id);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				League dto = new League();
				dto.setId(league_id);
				dto.setName(rs.getString("league_name"));
				dto.setImage(rs.getString("league_image"));
				dto.setCountryId(rs.getInt("country_id"));
				dto.setCountryName(rs.getString("country_name"));
				return dto;
			}
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return null;
	}

	public ResultSet list(String search, int limit, int page) {
		String sql = "SELECT l.*,c.country_name,c.country_image FROM (tbl_country c INNER JOIN tbl_league l ON c.country_id=l.country_id)  where l.league_name like ? ORDER BY l.league_id limit ?,?";
		try {
			//this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "%" + search + "%");
			pstmt.setInt(2, (page - 1) * limit);
			pstmt.setInt(3, limit);
			ResultSet rs = pstmt.executeQuery();
			return rs;
		} catch (SQLException e) {
			Log.write(e);
		} 
		return null;
	}

	public ResultSet list(int limit, int page) {
		return list("", limit, page);
	}

	public int count(String search) {
		String sql = "select count(*) from tbl_league where league_name like ?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "%" + search + "%");
			ResultSet rs = pstmt.executeQuery();
			return rs.next() ? rs.getInt(1) : 0;
		} catch (SQLException e) {
			Log.write(e);
		}
		return 0;
	}

	public int count() {
		return count("");
	}
	
	public int getLastIndex(){
		String sql="select max(league_id) from tbl_league";
		try {
			this.connect();
			PreparedStatement pstmt=con.prepareStatement(sql);
			ResultSet rs=pstmt.executeQuery();
			if(rs.next()) 
				return rs.getInt(1);
		} catch (SQLException e) {
			Log.write(e);
		}
		return 0;
	}
public static void main(String[] args) {
	LeagueModel x=new LeagueModel();
	System.out.println(x.getLastIndex());
}
}
